package com.justonetech.db.controller;

import com.justonetech.core.controller.BaseCRUDActionController;
import com.justonetech.db.daoservice.ExamClassService;
import com.justonetech.db.daoservice.ExamCourseService;
import com.justonetech.db.daoservice.ExamScService;
import com.justonetech.db.daoservice.ExamStudentService;
import com.justonetech.db.domain.ExamClass;
import com.justonetech.db.domain.ExamCourse;
import com.justonetech.db.domain.ExamSc;
import com.justonetech.db.domain.ExamStudent;
import com.justonetech.db.manager.SimpleQueryManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * author:
 */
@Controller
public class ExamStudentController extends BaseCRUDActionController<ExamStudent> {
    private Logger logger = LoggerFactory.getLogger(ExamStudentController.class);

    @Autowired
    private ExamStudentService examStudentService;

    @Autowired
    private ExamClassService examClassService;

    @Autowired
    private ExamScService examScService;

    @Autowired
    private ExamCourseService examCourseService;

    @Autowired
    private SimpleQueryManager simpleQueryManager;


    /**
     * 列表显示页面
     *
     * @param model 。
     */
    @RequestMapping
    public String grid(Model model) {
        logger.debug("...........grid page enter!");

        String hql = "from ExamStudent order by code";
        List<ExamStudent> examStudents = examStudentService.findByQuery(hql);
        model.addAttribute("examStudents", examStudents);

        model.addAttribute("canEdit", true);

        return "view/exam/grid";
    }

    /**
     * 模糊查询功能
     *
     * @param model 。
     */
    @RequestMapping
    public String doFind(Model model, @RequestParam("findValue") String findValue) {
        //System.out.println(findValue); 成功得到参数

        //组装hql语句
        String hqlFind = "from ExamStudent where [where] order by code";
        //定义一个字符串用来替代hql语句中的占位符
        String where = "";
        //判断findValue是否为null或为空
        if (findValue == null || "".equals(findValue)) {
            String grid = grid(model);
            return grid;
        }
        //通过正则表达式判断finValue是否为数字
        boolean isNum = findValue.matches("[0-9]+");

        if (isNum) {
            int findValueInt = Integer.parseInt(findValue);
            where += "code = " + findValueInt;
        } else {
            where += " name like " + "'%" + findValue + "%'";

        }
        hqlFind = hqlFind.replace("[where]", where);

        List<ExamStudent> examStudents = examStudentService.findByQuery(hqlFind);
        model.addAttribute("examStudents", examStudents);

        return "view/exam/grid";
    }

    /**
     * 添加课程页面
     *
     * @param model 。
     */
    @RequestMapping
    public String toAdd(Model model) {
        logger.debug("...........grid page enter!");

        //找出所有班级数据返回到页面去
        String hqlClass = "from ExamClass order by id";
        List<ExamClass> examClasses = examClassService.findByQuery(hqlClass);
        model.addAttribute("examClasses", examClasses);

        //找出所有科目数据返回到页面去
        String hqlCourse = "from ExamCourse order by id";
        List<ExamCourse> examCourses = examCourseService.findByQuery(hqlCourse);
        model.addAttribute("examCourses", examCourses);

        return "view/exam/add";
    }


    /**
     * 新增录入页面
     * result: -1 --->  课程人数超过10
     * 1  --->  添加成功
     *
     * @param model 。
     */
    @RequestMapping
    public String input(Model model, @ModelAttribute("examStudent") ExamStudent examStudent,
                        HttpServletRequest request,
                        @RequestParam("courseName") Long course,
                        @RequestParam("examClassName") Long examClassName) {
        int result = 0;
        ExamStudent stuff = new ExamStudent();
        //进入方法成功
        //如需增加其他默认值请在此添加
        model.addAttribute("bean", stuff);

        //用数组去接受从页面传来的多门课程的数据
        String[] courses = request.getParameterValues("courseName");
        //System.out.println(course);
        //定义一个List集合 用于后面存储后面从数据库查询出来的集合
        List<ExamSc> ExamScs = null;

        //测试sout
        //System.out.println("-------------------->");

        String hql = "";
        for (int i = 0; i < courses.length; i++) {
            hql = "from ExamSc where course = " + courses[0];
            ExamScs = examScService.findByQuery(hql);

            int count = ExamScs.size();
            if (count >= 10) {
                result = -1;
                //找出所有班级数据返回到页面去
                String hqlClass = "from ExamClass order by id";
                List<ExamClass> examClasses = examClassService.findByQuery(hqlClass);
                model.addAttribute("examClasses", examClasses);

                //找出所有科目数据返回到页面去
                String hqlCourse = "from ExamCourse order by id";
                List<ExamCourse> examCourses = examCourseService.findByQuery(hqlCourse);
                model.addAttribute("examCourses", examCourses);

                model.addAttribute("result", result);
                return "view/exam/add";
            }

        }

        //将学生对象保存到数据库中
        //System.out.println("____________________"+examStudent.getId());  得到的结果是null
        /*
         *  先将传过来的学号去数据库中查询，判断是否为已经存在的对象
         *  是 ———>  不需要再Exam_Student表中存入该数据
         *  否 ———>  需要重新存入
         */
        String hqlExist = "from ExamStudent where code = " + examStudent.getCode();
        List<ExamStudent> examStudentUniqe = examStudentService.findByQuery(hqlExist);

        ExamStudent examStudentExist = examStudentService.get(examStudentUniqe.get(0).getId());
        //System.out.println(examStudentExist.getCode()+"----->"+examStudent.getId());
        if (examStudentExist.getCode() != examStudent.getCode()) {
            //存入新学生
            examStudentService.save(examStudentExist);
        }

        //查出学生id，将学生和课程编号组成examSc对象，并保存到数据库中sc表中
        //String hqlStudent ="from ExamStudent where code = " + examStudent.getCode();
        //List<ExamStudent> examStudents = examStudentService.findByQuery(hqlStudent);
        //int id = examStudents.get(0).getId().intValue();

        //组装examSc对象，直接将该对象存入数据库
        ExamSc examSc = new ExamSc();
        ExamClass examClass = examClassService.get(examClassName);
        examStudent.setExamClass(examClass);

        examSc.setStudent(examStudentExist);
        ExamCourse examCourse = examCourseService.get(course);
        examSc.setCourse(examCourse);
        examScService.save(examSc);

        result = 1;
        return "view/exam/inputSuccess";
    }

    /**
     * 个人编辑界面
     *
     * @param model 。
     */
    @RequestMapping
    public String edit(Model model, @RequestParam("id") Long id) {
        //测试code数据有没传入后台（true)
        //System.out.println(code);
        String hqlExamStudent = "from ExamStudent where id = " + id;
        List<ExamStudent> examStudents = examStudentService.findByQuery(hqlExamStudent);
        model.addAttribute("examStudent", examStudents.get(0));

        //System.out.println(examStudents.get(0).getExamClass().getName());
        //找出所有科目数据返回到页面去
        String hqlClass = "from ExamClass order by id";
        List<ExamCourse> examClasses = examCourseService.findByQuery(hqlClass);
        model.addAttribute("examClasses", examClasses);

        return "view/exam/edit";
    }

    /**
     * 个人编辑操作
     *
     * @param model 。
     */
    @RequestMapping
    public String doEdit(Model model, @ModelAttribute("examStudent") ExamStudent examStudent,
                         @RequestParam("id") Long id,
                         @RequestParam("examClassName") Long examClassName) {
        //System.out.println(examStudent.getName()+"\t"+examStudent.getCode()+examStudent.getBornDate()+examStudent.getSex());
        //System.out.println(examStudent.getId());
        String hqlClass = "from ExamClass where id = " + examClassName;
        List<ExamClass> examClasses = examClassService.findByQuery(hqlClass);
        //System.out.println(examClasses.get(0).getName());
        examStudent.setExamClass(examClasses.get(0));


        //System.out.println(examStudent.getName()+"\t"+examStudent.getCode()+examStudent.getBornDate()+"\t"+examStudent.getSex()
        //        +"\t"+examStudent.getExamClass().getName()+"\t"+examStudent.getExamClass().getId());
        examStudentService.save(examStudent);

        return "view/exam/doEditSuccess";
    }

    /**
     * 个人日程界面
     *
     * @param model 。
     */
    @RequestMapping
    public String schedule(Model model, @RequestParam("id") Long id) {
        //String sql = "SELECT SCHE.*,DULE.\"COUNT\" FROM\n" +
        //        "\t(SELECT ESC.COURSE_ID,ESC.STUDENT_ID,EC.DAY_OF_WEEK,EC.END_TIME,EC.IS_MAIN,EC.LIMIT_COUNT,EC.NAME,EC.START_TIME,EC.TECHER FROM\n" +
        //        "\t\tEXAM_COURSE ec , EXAM_SC esc \n" +
        //        "\tWHERE EC.\"ID\" = ESC.COURSE_ID AND ESC.STUDENT_ID = "+id+"\n" +
        //        "\tORDER BY EC.DAY_OF_WEEK , EC.START_TIME) SCHE \n" +
        //        " LEFT OUTER JOIN \n" +
        //        "\t(SELECT count(*) COUNT , ESC.COURSE_ID FROM\n" +
        //        "\t\tEXAM_SC esc \n" +
        //        "\tGROUP BY esc.course_id\n" +
        //        "\tORDER BY esc.course_id) DULE\n" +
        //        "\tON SCHE.COURSE_ID = DULE.COURSE_ID ";
        //List scheduleAndCount = simpleQueryManager.getListBySql(sql);
        //model.addAttribute("scheduleAndCount",scheduleAndCount);
        //System.out.println("---------------------->"+scheduleAndCount.get(0));


        //依次取得该学生从星期一到星期六的日程
        String hqlScheduleMonday = "select esc from ExamSc esc , ExamCourse ec where esc.course.id = ec.id and esc.student.id = " + id + " and ec.dayOfWeek = 1 order by ec.startTime ";
        List<ExamSc> examScMondays = examScService.findByQuery(hqlScheduleMonday);
        model.addAttribute("examScMondays", examScMondays);

        String hqlScheduleTuesday = "select esc from ExamSc esc , ExamCourse ec where esc.course.id = ec.id and esc.student.id = " + id + " and ec.dayOfWeek = 2 order by ec.startTime ";
        List<ExamSc> examScTuesdays = examScService.findByQuery(hqlScheduleTuesday);
        model.addAttribute("examScTuesdays", examScTuesdays);

        String hqlScheduleWednesday = "select esc from ExamSc esc , ExamCourse ec where esc.course.id = ec.id and esc.student.id = " + id + " and ec.dayOfWeek = 3 order by ec.startTime ";
        List<ExamSc> examScWednesdays = examScService.findByQuery(hqlScheduleWednesday);
        model.addAttribute("examScWednesdays", examScWednesdays);

        String hqlScheduleThursday = "select esc from ExamSc esc , ExamCourse ec where esc.course.id = ec.id and esc.student.id = " + id + " and ec.dayOfWeek = 4 order by ec.startTime ";
        List<ExamSc> examScThursdays = examScService.findByQuery(hqlScheduleThursday);
        model.addAttribute("examScThursdays", examScThursdays);

        String hqlScheduleFriday = "select esc from ExamSc esc , ExamCourse ec where esc.course.id = ec.id and esc.student.id = " + id + " and ec.dayOfWeek = 5 order by ec.startTime ";
        List<ExamSc> examScFridays = examScService.findByQuery(hqlScheduleFriday);
        model.addAttribute("examScFridays", examScFridays);

        String hqlScheduleSaturday = "select esc from ExamSc esc , ExamCourse ec where esc.course.id = ec.id and esc.student.id = " + id + " and ec.dayOfWeek = 6 order by ec.startTime ";
        List<ExamSc> examScSaturdays = examScService.findByQuery(hqlScheduleSaturday);
        model.addAttribute("examScSaturdays", examScSaturdays);



        //查询出所有课程人数的统计数
        String sqlScCount = "SELECT count(*) count  FROM EXAM_SC esc GROUP BY esc.course_id ORDER BY esc.course_id";
        List examScCounts = examScService.findBySql(sqlScCount);
        Map<Integer,String> mapCounts = new HashMap<Integer, String>();
        for (int i=0;i<examScCounts.size();i++){
            mapCounts.put(i+1,examScCounts.get(i).toString());
        }
        model.addAttribute("mapCounts",mapCounts);

        return "view/exam/schedule";
    }


}